CREATE PROCEDURE [dbo].[eod_symbols_api]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- 1. Step
	-- Make sure there are no duplicate entries in table (or merge will fail)
    WITH source AS (
		SELECT	  sym_cd
				, sym_exc_cd
				, rn = ROW_NUMBER() OVER (PARTITION BY sym_cd, sym_exc_cd ORDER BY sym_load_ts DESC)
		FROM	raw.eod_symbols
	)
	DELETE FROM source WHERE rn > 1;

	-- 2. Step
	-- Merge data Symbol into into final table
	MERGE INTO	fin.eod_symbols WITH (HOLDLOCK) AS target
	USING		(SELECT	      s.sym_id
							, s.sym_cd
							, s.sym_desc
							, s.sym_exc_cd
							, e.exc_id
							, s.sym_load_ts 
				FROM		raw.eod_symbols s
				INNER JOIN	fin.eod_exchanges e 
							ON	(s.sym_exc_cd = e.exc_cd)) AS source
	ON				target.sym_cd = source.sym_cd
				AND	target.sym_exc_id = source.exc_id
	-- 2a: Existing symbol -> update data
	WHEN MATCHED THEN
		UPDATE SET	  target.sym_desc = source.sym_desc
					, target.sym_last_load_ts = source.sym_load_ts
					, target.sym_valid_til_dt = CONVERT(DATETIME, '99991231', 112)
	-- 2b: New symbol -> insert data
	WHEN NOT MATCHED BY TARGET THEN
		INSERT	( sym_cd
				, sym_desc
				, sym_exc_id
				, sym_valid_frm_dt
				, sym_valid_til_dt
				, sym_last_load_ts )
		VALUES	( sym_cd
				, sym_desc
				, exc_id
				, CAST(GETDATE() AS DATE)
				, CONVERT(DATETIME, '99991231', 112)
				, sym_load_ts );
	-- 2c: Terminated symbol -> invalidate
	-- Oopsie, that statement terminates all symbols in other exchanges...
	--WHEN NOT MATCHED BY SOURCE THEN
		--UPDATE SET	  sym_valid_til_dt = CAST(GETDATE() AS DATE);
	
	-- 3: Update Yahoo stuff for new symbols
	UPDATE	sym
	SET		  sym_yahoo_cd = CONCAT(sym_cd, yho_exc_ext)
			, sym_yahoo_fg = 1
			-- Bug Fix 4: Use new attribute sym_yahoo_procs_fg
			, sym_yahoo_procs_fg = 0
	FROM	fin.eod_symbols sym
			INNER JOIN fin.eod_exchanges exc 
				ON (sym_exc_id = exc_id)
			INNER JOIN (SELECT	  exc_cd AS yho_exc_cd
								, CASE exc_cd 
									WHEN 'AMS' THEN '.AS'
									WHEN 'ASX' THEN '.AX'
									WHEN 'BRU' THEN '.BR'
									WHEN 'HKEX' THEN '.HK'
									WHEN 'LIS' THEN '.LS'
									WHEN 'LSE' THEN '.L'
									WHEN 'MLSE' THEN '.MI'
									WHEN 'NZX' THEN '.NZ'
									WHEN 'PAR' THEN '.PA'
									WHEN 'SGX' THEN '.SI'
									WHEN 'TSK' THEN '.TO'
									WHEN 'TSXV' THEN '.V'
									ELSE NULL
								  END as yho_exc_ext
						FROM	fin.eod_exchanges) a 
				ON (exc_cd = a.yho_exc_cd)
	WHERE	sym_yahoo_cd IS NULL 
			AND sym_yahoo_fg IS NULL
			AND exc_cd IN ('NYSE', 'NZX', 'AMEX', 'ASX', 'HKEX', 'LSE', 'MLSE', 'PAR', 'LIS', 'TSX', 'TSXV', 'AMS', 'BRU', 'NASDAQ', 'SGX');


END
